package com.aaa.book_mall.dao.impl;

import com.aaa.book_mall.dao.StaffDao;
import com.aaa.book_mall.entity.Staff;
import com.aaa.book_mall.util.JDBCUtil;
import org.apache.commons.beanutils.BeanUtils;
import org.springframework.stereotype.Repository;

import java.lang.reflect.InvocationTargetException;
import java.util.List;
import java.util.Map;

/**
 * @ClassName StaffDaoImpl
 * @Description ToDo
 * @Auther teacher Bai
 * @Date 2020/12/26 10:49
 */
@Repository
public class StaffDaoImpl implements StaffDao {

    @Override
    public Staff getStaffByStaffIdAndPassword(String staffid, String password) {
        String sql = "select * from t_staff where staffid=? and password=?";
        List<Map<String, Object>> maps = JDBCUtil.queryListMap(sql, staffid, password);
        if(maps.size()>0){
            Staff staff = new Staff();
            Map<String, Object> map = maps.get(0);
            try {
                BeanUtils.populate(staff,map);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
            return staff;
        }
        return null;
    }

    /*
     * 员工信息查询sql语句
     * 使用List<Map>查询
     * @param staff
     * @return
     */
    @Override
    public List<Map<String,Object>> getAllStaff(Staff staff) {
        StringBuilder sql = new StringBuilder("select st.id,st.staffId,st.staffName,st.password,st.phone,st.idCard,st.address,st.createdTime,st.status,st.roleId,st.deptid,ro.rname,de.dname,st.url from t_staff st inner join t_role ro on ro.rid=st.roleId inner join t_dept de on st.deptid=de.deptid and st.status=1");
        if(staff.getStaffName()!=null && !"".equals(staff.getStaffName())){
            sql.append(" where st.staffName like '%"+staff.getStaffName()+"%'");
        }
        sql.append(" order by id limit ?,? ");
        List<Map<String,Object>> maps = JDBCUtil.queryListMap(sql.toString(),(staff.getPage()-1)*staff.getLimit(),staff.getLimit());
        if(maps.size()>0){
            return maps;
        }
        return null;
    }

    @Override
    public int getStaffCount(Staff staff) {
        StringBuilder sql = new StringBuilder("select count(*) from t_staff");
        if(staff.getDname()!=null && !"".equals(staff.getDname())){
            sql.append(" WHERE staffName LIKE '%"+staff.getDname()+"%'");
        }
        //System.out.println(sql);
        List<List> lists = JDBCUtil.queryList(sql.toString());
        if(lists.size()>0){
            //因为是List<List>，所有两层数据，一层一层的获取，所以写两个get(0)
            Object o = lists.get(0).get(0);
            int i = Integer.parseInt(o + "");
            return i;
        }
        return 0;
    }

    @Override
    public int delById(Integer id) {
        String sql="delete from t_staff where id=?";
        int i = JDBCUtil.update(sql,id);
        return i;
    }

    @Override
    public int insertStaff(Staff staff) {
        System.out.println("----增加员工");
        String sql="insert into t_staff (id,staffId,staffName,password,phone,idCard,address,createdTime,status,roleId,deptid,url) values(null,?,?,?,?,?,?,?,?,?,?,?);";
        int i = JDBCUtil.update(sql,staff.getStaffId(),staff.getStaffName(),123456,staff.getPhone(),staff.getIdCard(),staff.getAddress(),staff.getCreatedTime(),1,staff.getRoleId(),staff.getDeptid(),staff.getUrl());
        return i;
        //String sql="insert into t_staff values(null,?,?,123123,?,?,?,?,null,1,?,?)";
        //int i = JDBCUtilDruid.update(sql,staff.getStaffId(),staff.getStaffName(),staff.getPassword(),staff.getPhone(),staff.getIdCard(),staff.getAddress(),staff.getCreatedTime(),staff.getResignedTime(),staff.getRoleId(),staff.getDeptid());
//        int i = JDBCUtilDruid.update(sql, staff.getStaffId(),staff.getStaffName(),staff.getPhone(),staff.getIdCard(),staff.getAddress(),staff.getCreatedTime(),staff.getRoleId(),staff.getDeptid(),staff.getUrl());

        //return i;
    }

    @Override
    public int updateStaff(Staff staff) {
        System.out.println("修改员工");
        String sql="update t_staff set staffId=?,staffName=?,phone=?,idCard=?,address=?,createdTime=?,roleId=?,deptid=? where id=?;";
        int i = JDBCUtil.update(sql,staff.getStaffId(),staff.getStaffName(),staff.getPhone(),staff.getIdCard(),staff.getAddress(),staff.getCreatedTime(),staff.getRoleId(),staff.getDeptid(),staff.getId());
        return i;
    }
    @Override
    public int updateLeaveStaff(Staff staff) {
        System.out.println("修改员工");
        String sql="update t_staff set staffId=?,staffName=?,phone=?,idCard=?,address=?,resignedTime=?,roleId=?,deptid=? where id=?;";
        int i = JDBCUtil.update(sql,staff.getStaffId(),staff.getStaffName(),staff.getPhone(),staff.getIdCard(),staff.getAddress(),staff.getResignedTime(),staff.getRoleId(),staff.getDeptid(),staff.getId());
        return i;
    }

    @Override
    public List<Map<String,Object>> getStaffId(Integer staffId) {
        String sql="select * from t_staff where  staffId=?";
        List<Map<String,Object>> maps = JDBCUtil.queryListMap(sql,staffId);
        if (maps.size()>0){
            return maps;
        }
        return null;
    }

    @Override
    public List<Map<String,Object>> getPhone(String phone) {
        String sql="select * from t_staff where phone=?   ";
        List<Map<String,Object>> maps = JDBCUtil.queryListMap(sql,phone);
        if (maps.size()>0){
            return maps;
        }
        return null;
    }

    @Override
    public List<Map<String,Object>> getIdCard(String idCard) {
        String sql="select * from t_staff where idCard=?   ";
        List<Map<String,Object>> maps = JDBCUtil.queryListMap(sql,idCard);
        if (maps.size()>0){
            return maps;
        }
        return null;
    }

    @Override
    public int updateStatus(Integer id, Integer status) {
        System.out.println("-----");

        String sql = "update t_staff set status=? where id=?";
        int i = 0;
        if(status==1){
            i = JDBCUtil.update(sql, 0, id);
        }else{
            i = JDBCUtil.update(sql,1,id);
        }
        return i;
    }

    /**
     * 离职员工查询
     * @param staff
     * @return
     */
    @Override
    public List<Map<String,Object>> getStaff(Staff staff) {
        StringBuilder sql = new StringBuilder("select st.id,st.staffId,st.staffName,st.password,st.phone,st.idCard,st.address,st.createdTime,st.resignedTime,st.status,st.roleId,st.deptid,ro.rname,de.dname,st.url from t_staff st inner join t_role ro on ro.rid=st.roleId inner join t_dept de on st.deptid=de.deptid and st.status=0");
        if(staff.getStaffName()!=null && !"".equals(staff.getStaffName())){
            sql.append(" where st.staffName like '%"+staff.getStaffName()+"%'");
        }
        sql.append(" order by id limit ?,? ");
        List<Map<String,Object>> maps = JDBCUtil.queryListMap(sql.toString(),(staff.getPage()-1)*staff.getLimit(),staff.getLimit());
        if(maps.size()>0){
            return maps;
        }
        return null;
    }

    /**
     * 离职员工 状态
     * @param id
     * @param status
     * @return
     */
    @Override
    public int updateStaffStatus(Integer id, Integer status) {
        String sql = "update t_staff set status=? where id=?";
        int i = 0;
        if(status==3){
            i = JDBCUtil.update(sql,2,id);
        }else {
            i = JDBCUtil.update(sql,3,id);
        }
        System.out.println(i);
        return i;
    }

    @Override
    public List<Map<String, Object>> getAllDept() {
        String sql="select deptid,dname from t_dept";
        List<Map<String, Object>> maps = JDBCUtil.queryListMap(sql);
        if(maps.size()>0){
            return maps;
        }
        return null;
    }

    @Override
    public List<Map<String, Object>> getAllRole() {
        String sql="select rid,rname from t_role";
        List<Map<String, Object>> maps = JDBCUtil.queryListMap(sql);
        if(maps.size()>0){
            return maps;
        }
        return null;
    }
}
